package com.aiyige.base.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.os.Parcelable;
import android.text.TextUtils;
import android.util.Log;

import com.aiyige.BuildConfig;
import com.aiyige.MyApp;
import com.aiyige.base.db.table.AdFile;
import com.aiyige.base.db.table.AdModel;
import com.aiyige.base.db.table.DownloadFile;
import com.aiyige.base.db.table.DownloadModel;
import com.aiyige.base.db.table.InfoModel;
import com.aiyige.base.db.table.LearnVideoModel;
import com.aiyige.base.db.table.MajorCourseDBModel;
import com.aiyige.base.db.table.NormalVideoModel;
import com.aiyige.base.db.table.PhotoModel;
import com.aiyige.base.db.table.TraingCardDBModel;
import com.aiyige.base.db.table.TraingClassDBModel;
import com.aiyige.base.db.table.UploadFile;
import com.aiyige.configs.MyConfig;
import com.aiyige.page.my.localVideo.LocalMovieInfo;
import com.j256.ormlite.android.AndroidDatabaseConnection;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.BaseDaoImpl;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.support.DatabaseConnection;
import com.j256.ormlite.table.DatabaseTableConfig;
import com.j256.ormlite.table.TableInfo;
import com.j256.ormlite.table.TableUtils;

import java.io.File;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import timber.log.Timber;

/**
 * Created by zhuyakun on 2017/11/3.
 */

public class InnerDBHelper extends OrmLiteSqliteOpenHelper {
    public static final String TAG = InnerDBHelper.class.getSimpleName();
    private static final int DB_VERSION = BuildConfig.dbVersion;
    private static InnerDBHelper instance = null;

    public InnerDBHelper(Context context) {
        super(context, context.getFilesDir() + File.separator + BuildConfig.dbName, null, DB_VERSION);
    }

    List<Class<? extends Parcelable>> tableList = Arrays.asList(
            AdFile.class,
            AdModel.class);

    public static InnerDBHelper getInstance() {
        if (instance == null) {
            instance = new InnerDBHelper(MyApp.getAppContext());
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
        for (Class<?> clazz : tableList) {
            createTableSilent(connectionSource, clazz);
        }
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        ConnectionSource cs = this.getConnectionSource();
        DatabaseConnection conn = cs.getSpecialConnection();
        boolean clearSpecial = false;
        if (conn == null) {
            conn = new AndroidDatabaseConnection(db, true, this.cancelQueriesEnabled);

            try {
                cs.saveSpecialConnection((DatabaseConnection) conn);
                clearSpecial = true;
            } catch (SQLException var11) {
                throw new IllegalStateException("Could not save special connection", var11);
            }
        }

        try {
            for (Class<?> clazz : tableList) {
                dropTableSilent(cs, clazz);
                createTableSilent(cs, clazz);
            }
        } finally {
            if (clearSpecial) {
                cs.clearSpecialConnection((DatabaseConnection) conn);
            }
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int oldVersion, int currentVersion) {
        for (Class<?> clazz : tableList) {
            migrateTable(this, connectionSource, clazz);
        }
    }

    public static <T> void migrateTable(OrmLiteSqliteOpenHelper helper, ConnectionSource connectionSource, Class<T> clazz) {
        try {
            String tableName = DatabaseTableConfig.extractTableName(clazz);
            if (TextUtils.isEmpty(tableName)) {
                return;
            }
            boolean tableExist = checkTableExists(connectionSource, tableName);
            if (tableExist) {
                Dao<T, ?> dao = helper.getDao(clazz);
                QueryBuilder queryBuilder = dao.queryBuilder();
                GenericRawResults<String[]> oldTableQueryResults = queryBuilder.queryRaw();
                String[] oldColumns = oldTableQueryResults.getColumnNames();
                oldTableQueryResults.close();
                String tempTableName = (tableName + "_OLD").toUpperCase();
                try {
                    boolean tempTableAlreadyExists = checkTableExists(connectionSource, tempTableName);

                    if (tempTableAlreadyExists) {
                        dao.executeRaw("DROP TABLE " + tempTableName);
                    }
                } catch (Throwable t) {
                }
                dao.executeRaw("ALTER TABLE '" + tableName + "' RENAME TO '" + tempTableName + "';");
                TableUtils.createTable(connectionSource, clazz);
                TableInfo tableInfo = new TableInfo(connectionSource, (BaseDaoImpl) dao, clazz);
                //loop around both sets of columns and build up a String listing the common column names
                StringBuilder columnNamesString = new StringBuilder();

                boolean first = true;
                for (String col : oldColumns) {
                    if (tableInfo.hasColumnName(col)) {
                        if (first == false) {
                            columnNamesString.append(", ");
                        }
                        first = false;
                        columnNamesString.append(col);
                    }
                }
                //build an SQL statement to copy data from matching columns from the old to new tables
                String statement = "INSERT INTO " + tableName + " (" + columnNamesString + ") SELECT " + columnNamesString + " FROM " + tempTableName;
                dao.executeRaw(statement);
                dao.executeRaw("DROP TABLE " + tempTableName);
            } else {
                TableUtils.createTableIfNotExists(connectionSource, clazz);
            }
        } catch (Exception e) {
            Timber.e("migrateTable error:" + Log.getStackTraceString(e));
            Timber.e("try create a new table:" + DatabaseTableConfig.extractTableName(clazz));
            dropTableSilent(connectionSource, clazz);
            createTableSilent(connectionSource, clazz);
        }
    }

    public static <T> void createTableSilent(ConnectionSource connectionSource, Class<T> clazz) {
        try {
            TableUtils.createTableIfNotExists(connectionSource, clazz);
        } catch (Exception e) {
            Timber.e("createTableSilent:" + Log.getStackTraceString(e));
        }
    }

    public static <T> void dropTableSilent(ConnectionSource connectionSource, Class<T> clazz) {
        try {
            TableUtils.dropTable(connectionSource, clazz, true);
        } catch (Exception e) {
            Timber.e("dropTableSilent:" + Log.getStackTraceString(e));
        }
    }

    private static boolean checkTableExists(final ConnectionSource connectionSource, final String tableName) throws Exception {
        boolean alreadyExists = false;
        DatabaseConnection databaseConnection = connectionSource.getReadOnlyConnection();
        Exception capturedException = null;
        try {
            alreadyExists = databaseConnection.isTableExists(tableName);
        } catch (Exception e) {
            capturedException = e;
        }
        if (capturedException != null) {
            throw capturedException;
        }
        return alreadyExists;

    }
}
